

/*********************************************************************************/
/*                                                                               */ 
/* Create basic data (demographics etc) that describes individuals in our sample */
/*                                                                               */ 
/*********************************************************************************/


libname raw 'G:\Data\Rawdata\707677';

libname stats 'G:\Data\Rawdata\707677\Eksterne data'; 

libname common 'G:\Data\Workdata\707677\Common\01Data\01SAS';



/*******************/
/* Load our sample */
/*******************/



/* Remove applicants after 2003 */


data list;

set common.applicants;

if pyear<2004;

run;






/* Remove individuals who died before first message */


data list;

set list;

if death_before=0;

run;



data list;

set list;

drop death_before tottime;

run;




/*************************************************************/
/* Create variable for year prior to debt relief application */
/*************************************************************/


data list;

set list;

aar=put(pyear-1,4.0);

run;






/*********************/
/* Data from idapall */
/*********************/




data idapall;

set raw.idapall;

keep pnr aar alder koen aegte_nr faelle_nr hfpria hfaudd dispon_ny KOEJD KOM LOENMV QAKTIVF_NY05 QPASSIV QPASSIVN PSTILL;


run;



proc datasets library=work nolist;

modify idapall;

attrib _all_ label='';

quit;





proc sql;

create table list as select *

from list as a left join

idapall as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




data alder;

set idapall;

keep pnr aar alder;

if alder~='';

run;



data koen;

set idapall;

keep pnr aar koen;

if koen~='';

run;



proc sort data=alder nodupkey;

by pnr;

run;



proc sort data=koen nodupkey;

by pnr;

run;




proc datasets library=work nolist;

delete idapall;

quit;



/*****************/
/* Data from bef */
/*****************/



data bef;

set raw.bef;

keep pnr aar koen ANTPERSF ANTBOERNF  FOED_DAG IE_TYPE;

run;



proc datasets library=work nolist;

modify bef;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

bef as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




data alder1;

set bef;

keep pnr aar foed_dag;

if foed_dag~=.;

run;



data koen1;

set bef;

keep pnr aar koen;

if koen~='';

run;




proc sort data=alder1 nodupkey;

by pnr;

run;



proc sort data=koen1 nodupkey;

by pnr;

run;





proc datasets library=work nolist;

delete bef;

quit;





/**********************************************/
/* Add data on sex and age when it is missing */
/**********************************************/




/* Age */


data alder;

set alder;

byear=aar-alder;

run;



data alder;

set alder;

keep pnr byear;

run;



data alder1;

set alder1;

byear=year(foed_dag);

run;



data alder1;

set alder1;

keep pnr byear;

run;



data age;

set alder alder1;

if byear~=.;

run;



proc sort data=age nodupkey;

by pnr;

run;




proc sql;

create table list as select *

from list as a left join

age as b on

a.pnr=b.pnr;

quit;





data age;

set list;

keep pnr aar alder byear;

run;



data age;

set age;

length newalder $2;

newalder=PUT(aar-byear,2.0);

run;



data age;

set age;

length newalder1 $3;

zero='0';

newalder1=CATT(zero,newalder);

run;



data age;

set age;

keep pnr aar alder newalder1 newalder;

run;



data age;

set age;

alder_new=alder;

if alder='' and newalder~=' .' then alder_new=newalder1;

run;



data age;

set age;

keep pnr alder_new;

run;



proc sql;

create table list as select *

from list as a left join

age as b on

a.pnr=b.pnr;

quit;



data list;

set list;

if alder='' then alder=alder_new;

run;




data list;

set list;

drop alder_new;

run;



/* Sex */



data koen1;

set koen1;

length koen_new $1.;

koen_new=PUT(koen,1.0);

run;



data koen1;

set koen1;

drop koen;

run; 



data koen1;

set koen1;

rename koen_new=koen;

run;



data sex;

set koen koen1;

run;



data sex;

set sex;

if koen~='';

run;



proc sort data=sex nodupkey;

by pnr;

run;



data sex;

set sex;

keep pnr koen;

run;



data sex;

set sex;

rename koen=koen_new;

run;



proc sql;

create table list as select *

from list as a left join 

sex as b on

a.pnr=b.pnr;

quit;




data list;

set list;

if koen='' and koen_new~='' then koen=koen_new;

run;




data list;

set list;

drop koen_new foed_dag byear;

run;






/*****************/
/* Data from ind */
/*****************/



data ind;

set raw.ind;

keep pnr aar ERHVERVSINDK_13 koejd qaktivf qaktivf_ny05 NETOVSKUD NETOVSKUD_GL NETOVSKUD_13 PANTGAELD BANKGAELD OBLGAELD;

if aar<2003;

run;



proc datasets library=work nolist;

modify ind;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

ind as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete ind;

quit;



/******************/
/* Data from Indk */
/******************/



data indk;

set raw.indk;

keep pnr aar PRIGALD GALDIO;

if aar<2003;

run;




proc datasets library=work nolist;

modify indk;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

indk as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete indk;

quit;




/******************/
/* Data from fain */
/******************/



data fain;

set raw.fain;

keep pnr aar civst;

if aar<2003;

run;





proc datasets library=work nolist;

modify fain;

attrib _all_ label='';

quit;




proc sql;

create table list as select *

from list as a left join

fain as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;





proc datasets library=work nolist;

delete fain;

quit;






/**********************/
/* Organize variables */
/**********************/



data list;

retain pnr granted year month aar koen alder aegte_nr faelle_nr CIVST kom HFAUDD HFPRIA ANTBOERNF ANTPERSF 

DISPON_NY LOENMV ERHVERVSINDK_13 

QAKTIVF QAKTIVF_NY05  QPASSIV QPASSIVN KOEJD ;

set list;

run;




/************************/
/* Create new variables */
/************************/



/* Men */


data list;

set list;

male=0;

if koen=1 then male=1;

run; 



/* Home owners */


data list;

set list;

home=.;

if KOEJD=0 then home=0;

if KOEJD>0 and KOEJD~=. then home=1;

run;



/* Legal marriage */



data list;

set list;

legmarried=.;

if civst='G' then legmarried=1;

if civst~='G' and civst~='' then legmarried=0;

run;



/* Years of schooling */


data list;

set list;

schooling=put(HFPRIA,3.0)/12;

run;



data list;

set list;

schooling=ROUND(schooling,0.1);

run;
 



data list;

retain pnr granted year month aar koen male alder aegte_nr faelle_nr CIVST legmarried kom HFAUDD HFPRIA schooling ANTBOERNF ANTPERSF 

DISPON_NY LOENMV ERHVERVSINDK_13  

QAKTIVF QAKTIVF_NY05  QPASSIV QPASSIVN KOEJD home;

set list;

run;




proc sort data=list;

by pnr;

run;




/* Assets, debt and wealth */


data list;

set list;

length AKTIV PASSIV FORMUE 8.0;


if aar<1997 then AKTIV=QAKTIVF;

if aar>1996 then AKTIV=QAKTIVF_NY05;


if aar<1997 then PASSIV=QPASSIV;

if aar>1996 then PASSIV=QPASSIVN;


FORMUE=AKTIV-PASSIV;

run;





/* Create new variable on secured debt */


data list;

set list;

mortgage=.;

if aar<1995 then mortgage=PRIGALD;

if aar>1994 then mortgage=OBLGAELD;

run;




/***************************************************************************/
/* Adjust all nominal variables for inflation (use 2020 price level 104.5) */
/***************************************************************************/




data list;

set list;

cpi=.;

if aar='1983' then cpi=45.7;

if aar='1984' then cpi=48.6;

if aar='1985' then cpi=50.9;

if aar='1986' then cpi=52.8;

if aar='1987' then cpi=54.9;

if aar='1988' then cpi=57.4;

if aar='1989' then cpi=60.1;

if aar='1990' then cpi=61.7;

if aar='1991' then cpi=63.2;

if aar='1992' then cpi=64.5;

if aar='1993' then cpi=65.3;

if aar='1994' then cpi=66.6;

if aar='1995' then cpi=68.0;

if aar='1996' then cpi=69.5;

if aar='1997' then cpi=71.0;

if aar='1998' then cpi=72.3;

if aar='1999' then cpi=74.1;

if aar='2000' then cpi=76.2;

if aar='2001' then cpi=78.0;

if aar='2002' then cpi=79.9;

if aar='2003' then cpi=81.6;


run; 




data list;

set list;

DISPON_NY=DISPON_NY*(104.5/cpi);

ERHVERVSINDK_13=ERHVERVSINDK_13*(104.5/cpi);

KOEJD=KOEJD*(104.5/cpi);

LOENMV=LOENMV*(104.5/cpi);

AKTIV=AKTIV*(104.5/cpi);

PASSIV=PASSIV*(104.5/cpi);

FORMUE=FORMUE*(104.5/cpi);

NETOVSKUD=NETOVSKUD*(104.5/cpi); 

NETOVSKUD_GL=NETOVSKUD_GL*(104.5/cpi);  

NETOVSKUD_13=NETOVSKUD_13*(104.5/cpi); 

PRIGALD=PRIGALD*(104.5/cpi);  

GALDIO=GALDIO*(104.5/cpi);  

PANTGAELD=PANTGAELD*(104.5/cpi);  

BANKGAELD=BANKGAELD*(104.5/cpi);  

OBLGAELD=OBLGAELD*(104.5/cpi);  

mortgage=mortgage*(104.5/cpi);  

run;



data list;

set list;

DISPON_NY=ROUND(DISPON_NY,1);

ERHVERVSINDK_13=ROUND(ERHVERVSINDK_13,1);

KOEJD=ROUND(KOEJD,1);

LOENMV=ROUND(LOENMV,1);

AKTIV=ROUND(AKTIV,1);

PASSIV=ROUND(PASSIV,1);

FORMUE=ROUND(FORMUE,1);

NETOVSKUD=ROUND(NETOVSKUD,1); 

NETOVSKUD_GL=ROUND(NETOVSKUD_GL,1); 

NETOVSKUD_13=ROUND(NETOVSKUD_13,1);

PRIGALD=ROUND(PRIGALD,1); 

GALDIO=ROUND(GALDIO,1); 

PANTGAELD=ROUND(PANTGAELD,1); 

BANKGAELD=ROUND(BANKGAELD,1); 

OBLGAELD=ROUND(OBLGAELD,1); 

mortgage=ROUND(mortgage,1); 

run;





/********************/
/* Sample selection */
/********************/


/* Remove individuals for whom we lack data on sex and age */


data list;

set list;

if alder~='';

run;




/* Save data to hard drive */



data common.list;

set list;

run;




proc datasets library=work nolist kill;

quit;













